package cn.buk.tms.eotms.dao;

import cn.buk.common.sc.Page;
import cn.buk.common.util.DateUtil;
import cn.buk.tms.eotms.entity.*;
import cn.buk.tms.eotms.sc.SearchBillInfoCriteria;
import cn.buk.tms.eotms.sc.SearchSettlementCriteria;
import org.springframework.stereotype.Component;

import jakarta.persistence.EntityManager;
import jakarta.persistence.criteria.*;
import jakarta.transaction.Transactional;

import java.util.ArrayList;
import java.util.List;

/**
 * @author yfdai
 */
@Component
@Transactional
public class BillDaoImpl extends AbstractDao implements BillDao {

    @Override
    public List<BillInfo> searchBills(final int ownerId, final int customerId, SearchBillInfoCriteria sc) {
        List<BillInfo> results = null;

        CriteriaBuilder cb = em.getCriteriaBuilder();
        CriteriaQuery<BillInfo> cq = cb.createQuery(BillInfo.class);
        Root<BillInfo> root = cq.from(BillInfo.class);
        root.alias("o");

        var predicates0 = getPredicates_searchBills(ownerId, customerId, sc, cb, root);


        cq.where(predicates0.toArray(new Predicate[predicates0.size()]));
        List<jakarta.persistence.criteria.Order> orderByes = new ArrayList<>();

        if (sc.getOrderBy() == 1) {
            orderByes.add(cb.asc(root.get("customerId")));
            orderByes.add(cb.asc(root.get("customerCode")));
            orderByes.add(cb.asc(root.get("costCenter")));
            orderByes.add(cb.asc(root.get("billType")));
            orderByes.add(cb.asc(root.get("opDate")));
        } else if (sc.getOrderBy() == 2) {
            orderByes.add(cb.asc(root.get("billType")));
        }

        orderByes.add(cb.asc(root.get("id")));

        cq.orderBy(orderByes);

        //计算根据条件查询得出的数据总数
        CriteriaQuery<Long> countQuery = cb.createQuery(Long.class);
        Root<BillInfo> countRoot = countQuery.from(BillInfo.class);
        countRoot.alias("o");

        var predicates1 = getPredicates_searchBills(ownerId, customerId, sc, cb, countRoot);


        countQuery.select(cb.count(countRoot)).where(predicates1.toArray(new Predicate[predicates1.size()]));
        Long count = em.createQuery(countQuery).getSingleResult();

        int maxResults = count.intValue();
        if (maxResults > 0) {
            Page page = sc.getPage();
            page.setRowCount(maxResults);

            results = em.createQuery(cq)
                    .setFirstResult(page.getFirstPosition())
                    .setMaxResults(page.getPageSize()).getResultList();

        }

        if (results == null) {
            results = new ArrayList<>();
        }

        return results;
    }

    private static List<Predicate> getPredicates_searchBills(int ownerId, int customerId, SearchBillInfoCriteria sc, CriteriaBuilder cb, Root<BillInfo> root) {
        List<Predicate> predicates = new ArrayList<>();

        Predicate where = cb.conjunction();
        predicates.add(where);

        where = cb.equal(root.get(BillInfo_.enterpriseId), ownerId);
        predicates.add(where);

        where = cb.equal(root.get(BillInfo_.customerId), customerId);
        predicates.add(where);


        if (sc.getBillNum().length() > 0) {
            where = cb.equal(root.get(BillInfo_.billNum), sc.getBillNum());
            predicates.add(where);
        }

        if (!"-1".equalsIgnoreCase(sc.getBillType())) {
            //账单类型
            where = cb.equal(root.get(BillInfo_.billType), sc.getBillType());
            predicates.add(where);
        }


        if (sc.getCostCenter().length() > 0) {
            where = cb.like(root.get(BillInfo_.costCenter), "%" + sc.getCostCenter() + "%");
            predicates.add(where);
        }

        if ("0".equalsIgnoreCase(sc.getBePaid())) {
            // 未支付
            where = cb.isNull(root.get(BillInfo_.bePaid));
            where = cb.or(where, cb.equal(root.get(BillInfo_.bePaid), "0"));
            predicates.add(where);
        } else if ("1".equalsIgnoreCase(sc.getBePaid())) {
            // 已支付
            where = cb.equal(root.get(BillInfo_.bePaid), "1");
            predicates.add(where);
        }

        if (sc.getPaymentStatus() == 0) {
            //未支出
            where = cb.isNull(root.get(BillInfo_.paymentStatus));
            where = cb.or(where, cb.equal(root.get(BillInfo_.paymentStatus), 0));
            predicates.add(where);
        } else if (sc.getPaymentStatus() == 1) {
            //已支出
            where = cb.equal(root.get(BillInfo_.paymentStatus), 1);
            predicates.add(where);
        }

        if (sc.getReviewed() == 0) {
            // 未审核的
            where = cb.notEqual(root.get(BillInfo_.audited), "1");
            where = cb.or(where, cb.isNull(root.get(BillInfo_.audited)));
            predicates.add(where);
        } else if (sc.getReviewed() == 1) {
            // 审核过的
            predicates.add(cb.equal(root.get(BillInfo_.audited), "1"));
        }

        if (sc.getSettled() == 0) {
            // 未结算的账单
            where = cb.isNull(root.get(BillInfo_.settlementNo));
            predicates.add(where);
        }


        if (sc.getBeginDate() != null && sc.getEndDate() != null) {
            where = cb.greaterThanOrEqualTo(root.get(BillInfo_.opDate), sc.getBeginDate());
            where = cb.and(where, cb.lessThanOrEqualTo(root.get(BillInfo_.opDate), sc.getEndDate()));
            predicates.add(where);
        } else if (sc.getBeginDate() != null) {
            where = cb.greaterThanOrEqualTo(root.get(BillInfo_.opDate), sc.getBeginDate());
            predicates.add(where);
        } else if (sc.getEndDate() != null) {
            where = cb.lessThanOrEqualTo(root.get(BillInfo_.opDate), sc.getEndDate());
            predicates.add(where);
        }

        if (sc.getEtdzDateBegin() != null && sc.getEtdzDateEnd() != null) {
            where = cb.greaterThanOrEqualTo(root.get(BillInfo_.etdzDate), sc.getEtdzDateBegin());
            where = cb.and(where, cb.lessThanOrEqualTo(root.get(BillInfo_.etdzDate), sc.getEtdzDateEnd()));
            predicates.add(where);
        } else if (sc.getEtdzDateBegin() != null) {
            where = cb.greaterThanOrEqualTo(root.get(BillInfo_.etdzDate), sc.getEtdzDateBegin());
            predicates.add(where);
        } else if (sc.getEtdzDateEnd() != null) {
            where = cb.lessThanOrEqualTo(root.get(BillInfo_.etdzDate), sc.getEtdzDateEnd());
            predicates.add(where);
        }

        // 审核日期
        if (sc.getAuditDateBegin() != null && sc.getAuditDateEnd() != null) {
            where = cb.greaterThanOrEqualTo(root.get(BillInfo_.auditDate), sc.getAuditDateBegin());
            where = cb.and(where, cb.lessThanOrEqualTo(root.get(BillInfo_.auditDate), sc.getAuditDateEnd()));
            predicates.add(where);
        } else if (sc.getAuditDateBegin() != null) {
            where = cb.greaterThanOrEqualTo(root.get(BillInfo_.auditDate), sc.getAuditDateBegin());
            predicates.add(where);
        } else if (sc.getAuditDateEnd() != null) {
            where = cb.lessThanOrEqualTo(root.get(BillInfo_.auditDate), sc.getAuditDateEnd());
            predicates.add(where);
        }

        // 结算日期
        if (sc.getSettleDateBegin() != null && sc.getSettleDateEnd() != null) {
            where = cb.greaterThanOrEqualTo(root.get(BillInfo_.settleDate), sc.getSettleDateBegin());
            where = cb.and(where, cb.lessThanOrEqualTo(root.get(BillInfo_.settleDate), sc.getSettleDateEnd()));
            predicates.add(where);
        } else if (sc.getSettleDateBegin() != null) {
            where = cb.greaterThanOrEqualTo(root.get(BillInfo_.settleDate), sc.getSettleDateBegin());
            predicates.add(where);
        } else if (sc.getSettleDateEnd() != null) {
            where = cb.lessThanOrEqualTo(root.get(BillInfo_.settleDate), sc.getSettleDateEnd());
            predicates.add(where);
        }

        if (sc.getPayType() > 0) {
            predicates.add(cb.equal(root.get(BillInfo_.payTypeNote), sc.getPayType() + ""));
        }

        if (sc.getRemark().length() > 0) {
            where = cb.like(root.get(BillInfo_.remark), "%" + sc.getRemark() + "%");
            predicates.add(where);
        }

        if (sc.getOrderNo().length() > 0) {
            predicates.add(cb.equal(root.get(BillInfo_.orderNo), sc.getOrderNo()));
        }

        if (sc.getFlightOrderNo().length() > 0) {
            predicates.add(cb.equal(root.get(BillInfo_.flightOrderNo), sc.getFlightOrderNo()));
        }


        if (sc.getIntlTicket() >= 0) {
            predicates.add(cb.equal(root.get(BillInfo_.intlTicket), sc.getIntlTicket()));
        }

        if (sc.getCostCenter().length() > 0) {
            where = cb.like(root.get(BillInfo_.costCenter), "%" + sc.getCostCenter() + "%");
            predicates.add(where);
        }

        if (sc.getSettlementNo().length() > 0) {
            where = cb.like(root.get(BillInfo_.settlementNo), "%" + sc.getSettlementNo() + "%");
            predicates.add(where);
        }
        return predicates;
    }

    @Override
    public BillInfo getBillInfo(int id) {
        return em.find(BillInfo.class, id);
    }

    @Override
    public List<BillFlightTicket> searchBillFlightTickets(int ownerId, String billNum) {
        List<BillFlightTicket> tickets = em.createQuery("select o from BillFlightTicket o where o.enterpriseId = :enterpriseId and o.billNum = :billNum order by o.ticketNo", BillFlightTicket.class)
                .setParameter("enterpriseId", ownerId)
                .setParameter("billNum", billNum)
                .getResultList();

        //获取票号下面的详情
        for (BillFlightTicket ticket : tickets) {
            List<BillFlightTicketDetail> details = searchBillFlightTicketDetails(em, ownerId, ticket);
            ticket.setDetails(details);
        }

        return tickets;
    }


    private List<BillFlightTicketDetail> searchBillFlightTicketDetails(EntityManager em, int enterpriseId, BillFlightTicket ticket) {
        return em.createQuery("select o from BillFlightTicketDetail o " +
                        " where o.enterpriseId = :enterpriseId and o.balanceCode = :balanceCode and o.ticketNo = :ticketNo order by o.segOrd", BillFlightTicketDetail.class)
                .setParameter("enterpriseId", enterpriseId)
                .setParameter("balanceCode", ticket.getBalanceCode())
                .setParameter("ticketNo", ticket.getTicketNo())
                .getResultList();
    }

    @Override
    public List<BillFlightRefundTicket> searchBillFlightRefundTickets(int ownerId, String billNum) {
        return em.createQuery("select o from BillFlightRefundTicket o where o.enterpriseId = :enterpriseId and o.billNum = :billNum order by o.ticketNo", BillFlightRefundTicket.class)
                .setParameter("enterpriseId", ownerId)
                .setParameter("billNum", billNum)
                .getResultList();
    }

    @Override
    public List<BillChangeOrder> searchBillChangeOrders(int ownerId, String billNum) {
        return em.createQuery("select o from BillChangeOrder o " +
                        "where o.enterpriseId = :enterpriseId and o.billNum = :billNum order by o.ticketNo", BillChangeOrder.class)
                .setParameter("enterpriseId", ownerId)
                .setParameter("billNum", billNum)
                .getResultList();
    }

    @Override
    public List<BillTrainTicket> searchBillTrainTickets(int ownerId, String billNum) {
        return em.createQuery("select o from BillTrainTicket o where o.enterpriseId = :enterpriseId and o.billNum = :billNum order by o.id", BillTrainTicket.class)
                .setParameter("enterpriseId", ownerId)
                .setParameter("billNum", billNum)
                .getResultList();
    }

    @Override
    public List<BillHotelBooking> searchBillHotelBookings(int ownerId, String billNum) {
        return em.createQuery("select o from BillHotelBooking o where o.enterpriseId = :enterpriseId and o.billNum = :billNum order by o.id", BillHotelBooking.class)
                .setParameter("enterpriseId", ownerId)
                .setParameter("billNum", billNum)
                .getResultList();
    }

    @Override
    public List<BillVasOrder> searchBillVasOrders(int ownerId, String billNum) {
        return em.createQuery("select o from BillVasOrder o where o.enterpriseId = :enterpriseId and o.billNum = :billNum order by o.id", BillVasOrder.class)
                .setParameter("enterpriseId", ownerId)
                .setParameter("billNum", billNum)
                .getResultList();
    }

    @Override
    public List<SettlementSheet> searchSettlements(final int ownerId, final int customerId, SearchSettlementCriteria sc) {
        List<SettlementSheet> results = null;

        CriteriaBuilder cb = em.getCriteriaBuilder();
        CriteriaQuery<SettlementSheet> cq = cb.createQuery(SettlementSheet.class);
        Root<SettlementSheet> root = cq.from(SettlementSheet.class);
        root.alias("o");

        var predicates0 = getPredicates_searchSettlements(ownerId, customerId, sc, cb, root);


        //计算根据条件查询得出的数据总数
        CriteriaQuery<Long> countQuery = cb.createQuery(Long.class);
        Root<SettlementSheet> countRoot = countQuery.from(SettlementSheet.class);
        countRoot.alias("o");

        var predicates1 = getPredicates_searchSettlements(ownerId, customerId, sc, cb, countRoot);

        countQuery.select(cb.count(countRoot)).where(predicates1.toArray(new Predicate[predicates1.size()]));
        Long count = em.createQuery(countQuery).getSingleResult();

        int maxResults = count.intValue();
        if (maxResults > 0) {
            cq.where(predicates0.toArray(new Predicate[predicates0.size()]));
            List<jakarta.persistence.criteria.Order> orderByes = new ArrayList<>();
            orderByes.add(cb.asc(root.get("paid")));
            orderByes.add(cb.desc(root.get("lastUpdate")));
            orderByes.add(cb.desc(root.get("id")));
            cq.orderBy(orderByes);

            Page page = sc.getPage();
            page.setRowCount(maxResults);

            results = em.createQuery(cq)
                    .setFirstResult(page.getFirstPosition())
                    .setMaxResults(page.getPageSize()).getResultList();
        }

        return results == null ? new ArrayList<>() : results;
    }

    private static List<Predicate> getPredicates_searchSettlements(int ownerId, int customerId, SearchSettlementCriteria sc, CriteriaBuilder cb, Root<SettlementSheet> root) {
        List<Predicate> predicates = new ArrayList<>();

        Predicate where = cb.conjunction();
        predicates.add(where);

        where = cb.equal(root.get(SettlementSheet_.enterpriseId), ownerId);
        predicates.add(where);

        where = cb.equal(root.get(SettlementSheet_.customerId), customerId);
        predicates.add(where);


        if (sc.getBeginDate() != null && sc.getEndDate() != null) {
            where = cb.greaterThanOrEqualTo(root.get(SettlementSheet_.createTime), sc.getBeginDate());
            where = cb.and(where, cb.lessThanOrEqualTo(root.get(SettlementSheet_.createTime), DateUtil.addDays(sc.getEndDate(), 1)));
            predicates.add(where);
        } else if (sc.getBeginDate() != null) {
            where = cb.greaterThanOrEqualTo(root.get(SettlementSheet_.createTime), sc.getBeginDate());
            predicates.add(where);
        } else if (sc.getEndDate() != null) {
            where = cb.lessThanOrEqualTo(root.get(SettlementSheet_.createTime), DateUtil.addDays(sc.getEndDate(), 1));
            predicates.add(where);
        }

        if (sc.getStatus() == 0) {
            // 未支付
            where = cb.equal(root.get(SettlementSheet_.paid), sc.getStatus());
            predicates.add(where);
        } else if (sc.getStatus() == 1) {
            // 已支付
            where = cb.equal(root.get(SettlementSheet_.paid), sc.getStatus());
            predicates.add(where);
        }

        if (sc.getSettlementNo().length() > 0) {
            where = cb.like(root.get(SettlementSheet_.settlementNo), "%" + sc.getSettlementNo() + "%");
            predicates.add(where);
        } else {
            where = cb.notEqual(root.get(SettlementSheet_.amount), 0);
            predicates.add(where);
        }

        if (sc.getAmount() > 0) {
            where = cb.equal(root.get(SettlementSheet_.amount), sc.getAmount());
            predicates.add(where);
        }
        return predicates;
    }

    @Override
    public SettlementSheet getSettlementSheetById(int id) {
        SettlementSheet ss = em.find(SettlementSheet.class, id);
        if (ss != null) {
            em.refresh(ss);
        }

        return ss;
    }

    @Override
    public List<BillVasOrder> searchVasOrdersBySettlementNo(int ownerId, String settlementNo) {
        return em.createQuery("select o from BillVasOrder o " +
                        "where o.enterpriseId = :enterpriseId and o.billNum in (" +
                        "select b.billNum from BillInfo b where b.enterpriseId = :enterpriseId and b.settlementNo = :settlementNo" +
                        ")  order by o.id", BillVasOrder.class)
                .setParameter("enterpriseId", ownerId)
                .setParameter("settlementNo", settlementNo)
                .getResultList();
    }

    @Override
    public List<BillHotelBooking> searchHotelOrdersBySettlementNo(int ownerId, String settlementNo) {
        return em.createQuery("select o from BillHotelBooking o " +
                        "where o.enterpriseId = :enterpriseId and o.billNum in (" +
                        "select b.billNum from BillInfo b where b.enterpriseId = :enterpriseId and b.settlementNo = :settlementNo" +
                        ")  order by o.id", BillHotelBooking.class)
                .setParameter("enterpriseId", ownerId)
                .setParameter("settlementNo", settlementNo)
                .getResultList();
    }

    @Override
    public List<BillTrainTicket> searchTrainTicketsBySettlementNo(int ownerId, String settlementNo) {
        return em.createQuery("select o from BillTrainTicket o " +
                        "where o.enterpriseId = :enterpriseId and o.billNum in (" +
                        "select b.billNum from BillInfo b where b.enterpriseId = :enterpriseId and b.settlementNo = :settlementNo" +
                        ")  order by o.id", BillTrainTicket.class)
                .setParameter("enterpriseId", ownerId)
                .setParameter("settlementNo", settlementNo)
                .getResultList();
    }

    @Override
    public List<BillFlightTicket> searchTicketsBySettlementNo(int ownerId, String settlementNo) {
        List<BillFlightTicket> tickets = em.createQuery("select o from BillFlightTicket o " +
                        "where o.enterpriseId = :enterpriseId and o.billNum in (" +
                        "select b.billNum from BillInfo b where b.enterpriseId = :enterpriseId and b.settlementNo = :settlementNo" +
                        ")  order by o.id", BillFlightTicket.class)
                .setParameter("enterpriseId", ownerId)
                .setParameter("settlementNo", settlementNo)
                .getResultList();


        //获取票号下面的详情(不能省略，前端需要用到）
        for (BillFlightTicket ticket : tickets) {
            List<BillFlightTicketDetail> details = searchBillFlightTicketDetails(em, ownerId, ticket);
            ticket.setDetails(details);
        }

        return tickets;
    }

    @Override
    public List<BillFlightRefundTicket> searchRefundsBySettlementNo(int ownerId, String settlementNo) {
        return em.createQuery("select o from BillFlightRefundTicket o " +
                        "where o.enterpriseId = :enterpriseId and o.billNum in (" +
                        "select b.billNum from BillInfo b where b.enterpriseId = :enterpriseId and b.settlementNo = :settlementNo" +
                        ")  order by o.ticketNo", BillFlightRefundTicket.class)
                .setParameter("enterpriseId", ownerId)
                .setParameter("settlementNo", settlementNo)
                .getResultList();
    }

    @Override
    public List<BillChangeOrder> searchChangesBySettlementNo(int ownerId, String settlementNo) {
        return em.createQuery("select o from BillChangeOrder o " +
                        "where o.enterpriseId = :enterpriseId and o.billNum in (" +
                        "select b.billNum from BillInfo b where b.enterpriseId = :enterpriseId and b.settlementNo = :settlementNo" +
                        ")  order by o.ticketNo", BillChangeOrder.class)
                .setParameter("enterpriseId", ownerId)
                .setParameter("settlementNo", settlementNo)
                .getResultList();
    }
}
